package edu.umn.contactviewer.datastore.impl;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import edu.umn.contactviewer.Contact;

public class ContactListDatabaseAdapter {
	private static final String TABLE_NAME = "contact";
	private static final String COLUMN_ID = "_id";
	private static final String COLUMN_NAME = "name";
	private static final String COLUMN_PHONE = "phone";
	private static final String COLUMN_TITLE = "title";
	private static final String COLUMN_EMAIL = "email";
	private static final String COLUMN_TWITTER_ID = "twiter_id";
	private static String ALL_COLUMNS[] = { COLUMN_ID, COLUMN_NAME,
			COLUMN_PHONE, COLUMN_TITLE, COLUMN_EMAIL, COLUMN_TWITTER_ID };

	private static final String DATABASE_NAME = "contacts.db";
	private static final int DATABASE_VERSION = 1;

	// Database creation sql statement
	private static final String DATABASE_CREATE = "CREATE TABLE " + TABLE_NAME
			+ "( " + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ COLUMN_NAME + " TEXT NOT NULL, " + COLUMN_PHONE + " TEXT, "
			+ COLUMN_TITLE + " TEXT, " + COLUMN_EMAIL + " TEXT, "
			+ COLUMN_TWITTER_ID + " TEXT);";
	
	private static final String ORDER_BY = COLUMN_NAME + " ASC";

	final ContactListDatabaseHelper contactListDatabaseHelper;

	public ContactListDatabaseAdapter(Context context) {
		contactListDatabaseHelper = new ContactListDatabaseHelper(context);
	}

	public List<Contact> getAllContacts() {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase database = null;
		try {
			database = contactListDatabaseHelper.getReadableDatabase();
			Cursor cursor = database.query(TABLE_NAME, ALL_COLUMNS, null, null,
					null, null, ORDER_BY);
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				Contact contact = cursorToContact(cursor);
				contacts.add(contact);
				cursor.moveToNext();
			}
			// Make sure to close the cursor
			cursor.close();
		} catch (SQLiteException exception) {
			Log.e(getClass().getName(), exception.toString());
		} finally {
			if (null != database) {
				database.close();
				database = null;
			}
		}

		return contacts;
	}

	public Boolean insertContact(Contact contact) {
		long insertID = -1;
		SQLiteDatabase database = null;
		try {
			database = contactListDatabaseHelper.getWritableDatabase();
			ContentValues contactContentValues = buildContentValuesForInsert(contact);
			insertID = database.insert(TABLE_NAME, null, contactContentValues);
		} catch (SQLiteException exception) {
			Log.e(getClass().getName(), exception.toString());
		} finally {
			if (null != database) {
				database.close();
				database = null;
			}
		}

		return (-1 != insertID);
	}

	public Boolean updateContact(Contact contact) {
		ContentValues contactContentValues = buildContentValuesForInsert(contact);
		long rowsAffected = 0;
		SQLiteDatabase database = null;
		try {
			database = contactListDatabaseHelper.getWritableDatabase();
			rowsAffected = database.update(TABLE_NAME, contactContentValues,
					createContactEqualityWhereClause(contact), null);

		} catch (SQLiteException exception) {
			Log.e(getClass().getName(), exception.toString());
		} finally {
			if (null != database) {
				database.close();
				database = null;
			}
		}

		return (0 != rowsAffected);
	}

	public Boolean deleteContact(Contact contact) {
		long rowsAffected = 0;
		SQLiteDatabase database = null;
		try {
			database = contactListDatabaseHelper.getWritableDatabase();
			rowsAffected = database.delete(TABLE_NAME,
					createContactEqualityWhereClause(contact), null);

		} catch (SQLiteException exception) {
			Log.e(getClass().getName(), exception.toString());
		} finally {
			if (null != database) {
				database.close();
				database = null;
			}
		}

		return (0 != rowsAffected);
	}

	private String createContactEqualityWhereClause(Contact contact) {
		return COLUMN_ID + "=" + contact.get_id();
	}

	private ContentValues buildContentValuesForInsert(Contact contact) {
		ContentValues values = new ContentValues();
		values.put(COLUMN_NAME, contact.getName());
		values.put(COLUMN_PHONE, contact.getPhone());
		values.put(COLUMN_TITLE, contact.getTitle());
		values.put(COLUMN_EMAIL, contact.getEmail());
		values.put(COLUMN_TWITTER_ID, contact.getTwitterId());

		return values;
	}

	private Contact cursorToContact(Cursor cursor) {
		Contact contact = new Contact(cursor.getString(1));
		contact.set_id(cursor.getString(0));
		contact.setPhone(cursor.getString(2));
		contact.setTitle(cursor.getString(3));
		contact.setEmail(cursor.getString(4));
		contact.setTwitterId(cursor.getString(5));

		return contact;
	}

	// helper class for creating Contact table
	private class ContactListDatabaseHelper extends SQLiteOpenHelper {
		public ContactListDatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase database) {
			Log.i(getClass().getName(), DATABASE_CREATE);
			database.execSQL(DATABASE_CREATE);

			insertTestData(database);
		}

		@Override
		public void onUpgrade(SQLiteDatabase database, int oldVersion,
				int newVersion) {
			Log.wtf(ContactListDatabaseHelper.class.getName(),
					"Upgrading database from version " + oldVersion + " to "
							+ newVersion + ", which will destroy all old data");
			database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
			onCreate(database);
		}

		private void insertTestData(SQLiteDatabase database) {
			// insert test data // make some contacts
			ArrayList<Contact> contacts = new ArrayList<Contact>();
			contacts.add(new Contact("Malcom Reynolds")
					.setEmail("mal@serenity.com").setTitle("Captain")
					.setPhone("612-555-1234").setTwitterId("malcomreynolds"));
			contacts.add(new Contact("Zoe Washburne")
					.setEmail("zoe@serenity.com").setTitle("First Mate")
					.setPhone("612-555-5678").setTwitterId("zoewashburne"));
			contacts.add(new Contact("Hoban Washburne")
					.setEmail("wash@serenity.com").setTitle("Pilot")
					.setPhone("612-555-9012").setTwitterId("wash"));
			contacts.add(new Contact("Jayne Cobb")
					.setEmail("jayne@serenity.com").setTitle("Muscle")
					.setPhone("612-555-3456").setTwitterId("heroofcanton"));
			contacts.add(new Contact("Kaylee Frye")
					.setEmail("kaylee@serenity.com").setTitle("Engineer")
					.setPhone("612-555-7890").setTwitterId("kaylee"));
			contacts.add(new Contact("Simon Tam")
					.setEmail("simon@serenity.com").setTitle("Doctor")
					.setPhone("612-555-4321").setTwitterId("simontam"));
			contacts.add(new Contact("River Tam")
					.setEmail("river@serenity.com").setTitle("Doctor's Sister")
					.setPhone("612-555-8765").setTwitterId("miranda"));
			contacts.add(new Contact("Shepherd Book")
					.setEmail("shepherd@serenity.com").setTitle("Shepherd")
					.setPhone("612-555-2109").setTwitterId("shepherdbook"));

			for (Contact contact : contacts) {
				ContentValues contactContentValues = buildContentValuesForInsert(contact);
				database.insert(TABLE_NAME, null, contactContentValues);
			}// end for (Contact contact : contacts) {
		}

	}// end ContactListDataBaseHelper
}